﻿create procedure [String].[Search] ( @p_QueryString nvarchar(max) )
as
begin

  set nocount on;

  create table #SearchResults ( 
    FieldInformation nvarchar(250), 
    FieldValue nvarchar(3750) 
  );

  declare @TableName nvarchar(256) = '';
  declare @SearchString nvarchar(110) = QuoteName( '%' + @p_QueryString+ '%', '''' ); 
  declare @FieldInformation nvarchar(128);

  while ( @TableName Is Not Null )
  begin
    
    set @FieldInformation = '';
    
    set @TableName = (
      select Min( QuoteName( TABLE_SCHEMA ) + '.' + QuoteName( TABLE_NAME ) )
       from INFORMATION_SCHEMA.TABLES
      where TABLE_TYPE = 'BASE TABLE'
        and QuoteName( TABLE_SCHEMA ) + '.' + QuoteName( TABLE_NAME ) > @TableName
        and ObjectProperty( Object_Id( QuoteName( TABLE_SCHEMA ) + '.' + QuoteName( TABLE_NAME ) ), 'IsMSShipped' ) = 0
    );

    while ( ( @TableName Is Not Null ) And ( @FieldInformation Is Not Null ) )
    begin
    
      set @FieldInformation = (
        select Min( QuoteName( COLUMN_NAME ) )
         from INFORMATION_SCHEMA.COLUMNS
        where TABLE_SCHEMA = ParseName( @TableName, 2 )
          and TABLE_NAME = ParseName( @TableName, 1 )
          and DATA_TYPE In ('char', 'varchar', 'nchar', 'nvarchar')
          and QuoteName( COLUMN_NAME ) > @FieldInformation
      );

      if ( @FieldInformation Is Not Null )
      begin
        insert into #SearchResults
        execute
        (
          'select ''' + @TableName + '.' + @FieldInformation + ''', Left(' + @FieldInformation + ', 3000)
             from ' + @TableName + ' with (nolock) ' +
          ' where ' + @FieldInformation + ' like ' + @SearchString
        );
      end

    end

  end

  select * from #SearchResults;

  drop table #SearchResults;

end